package com.spar.openprojects.server;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.spar.openprojects.server.datasource.DatasourceInformixETime;
import com.spar.openprojects.server.datasource.DatasourceMySqlOpenprojects;
import com.spar.openprojects.shared.ETimeProject;
import com.spar.openprojects.shared.Project;
import com.spar.openprojects.shared.rowmapper.ETimeProjectRowMapper;
import com.spar.openprojects.shared.rowmapper.ProjectRowMapper;
import com.sql.database.Database;
import com.sql.database.IConnectionFactory;
import com.sql.database.IRowMapper;

public class ExportDataServiceProvider extends HttpServlet {
	/**
	 * 
	 */
	private static final long serialVersionUID = -3768817950809784492L;

	@SuppressWarnings("unchecked")
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		try {
			ServletOutputStream op = response.getOutputStream();
			response.setContentType("application/octet-stream");
			response.setHeader("Content-Disposition",
					"attachment; filename=\"Test.csv\"");
			IConnectionFactory connectionFactory = new DatasourceMySqlOpenprojects();
			Database database = new Database(connectionFactory);
			IRowMapper rowMapper = new ProjectRowMapper();
			String sqlStatement = "select sum(o.order_amount) as sum_order_amount, p.id,p.number,p.description,p.external_costs,p.internal_days,p.member_id,m.id, m.first_name, m.last_name, m.number from project as p inner join member as m on p.member_id = m.id left join openprojects.order as o on o.project_id = p.id group by p.id,p.number,p.description,p.external_costs,p.internal_days,p.member_id,m.id, m.first_name, m.last_name";
			List<Project> projects = new ArrayList<Project>();
			try {
				projects = database.ExecuteQuery(sqlStatement, rowMapper);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			// etime data
			String projectIds = "";
			for (Project project : projects) {
				if (project.getNumber() != null && project.getNumber() != 0) {
					projectIds += project.getNumber() + ",";
				}
			}
			if (projectIds.length() > 0)
				projectIds = projectIds.substring(0, projectIds.length() - 2);
			sqlStatement = "select c.cat_id project_id, round(sum(t.duration)/8,2) time from time t inner join subcategory s on t.sc_id = s.id inner join category c on c.cat_id = s.cat_id where c.cat_id in ("
					+ projectIds
					+ ") and s.cat_id in ("
					+ projectIds
					+ ") and (t.abt_kbez like 'ICS%' or t.abt_kbez like 'HZ4%') and t.valid = 1 group by c.cat_id order by c.cat_id desc";
			connectionFactory = new DatasourceInformixETime();
			database = new Database(connectionFactory);
			rowMapper = new ETimeProjectRowMapper();
			List<ETimeProject> eTimeProjects = new ArrayList<ETimeProject>();
			try {
				eTimeProjects = database.ExecuteQuery(sqlStatement, rowMapper);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			for (ETimeProject etp : eTimeProjects) {
				for (Project p : projects) {
					if (etp.getNumber().equals(p.getNumber())) {
						p.setInternalDaysUsed(etp.getInternalDays());
						break;
					}

				}
			}
			StringBuffer line = new StringBuffer();
			line.append("projectnumber;projectdescription;responsible;external costs ordered; external costs; internal days used; internal days\n");
			for (Project project : projects) {
				line.append(project.getNumber() + ";");
				line.append(project.getDescription() + ";");
				line.append(project.getMember().getLastName() + ";");
				line.append(project.getExternalCostsOrdered() + ";");
				line.append(project.getExternalCosts() + ";");
				line.append(project.getInternalDaysUsed() + ";");
				line.append(project.getInternalDays() + "\n");
			}
			op.write(line.toString().getBytes());
			op.flush();
			op.close();

		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}
}
